package com.ztjc.day03_sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

import com.ztjc.day03_sqlite.db.PersonOpenHelper;

/**
 * Created by pro on 15/10/8.
 */
public class SqliteTest extends AndroidTestCase {

    PersonOpenHelper openHelper;
    SQLiteDatabase db;

    @Override
    protected void setUp() throws Exception {
        openHelper = new PersonOpenHelper(getContext());
        db = openHelper.getWritableDatabase();
    }

    /**
     * 插入数据
     */
    public void testInsertSq() {
        for (int i = 0; i < 20; i ++) {
            db.execSQL("insert into person(name, salary, phone) values(?, ?, ?)", new String[]{"张三" + i, "15000", "1511697078" + i % 10});
        }
    }

    /**
     * 更新数据
     */
    public void testUpdateSql() {
        db.execSQL("update person set name = ? where _id = ?", new String[]{"小三", "1"});
    }

    /**
     * 删除数据
     */
    public void testDeleteSql() {
        db.execSQL("delete from person where _id = ?", new String[]{"2"});
    }

    /**
     * 查询数据
     */
    public void testQuerySql() {
        Cursor cursor = db.rawQuery("select * from person", null);
        while (cursor.moveToNext()) {
            System.out.println(cursor.getString(cursor.getColumnIndex("name")));
        }
    }

    /**
     * 插入数据
     */
    public void testInsertApi() {
        ContentValues values = new ContentValues();
        values.put("name", "小四");
        values.put("salary", "16000");
        values.put("phone", "15116980788");
        db.insert("person", null, values);
    }

    /**
     * 更新数据
     */
    public void testUpdateApi() {
        ContentValues values = new ContentValues();
        values.put("name", "老大");
        db.update("person", values, "_id = ?", new String[]{"3"});
    }

    /**
     * 删除数据
     */
    public void testDeleteApi() {
        db.delete("person", "_id = ?", new String[]{"4"});
    }

    /**
     * 查询数据
     */
    public void testQueryApi() {
        Cursor person = db.query("person", null, null, null, null, null, null);
        while (person.moveToNext()) {
            System.out.println(person.getString(person.getColumnIndex("name")));
        }
    }

    /**
     * 测试事务
     */
    public void testTranstation() {
        try {
            db.beginTransaction();

            db.execSQL("update person set salary = ? where _id = ?", new String[]{"29000", "1"});

            //int i = 3 / 0;

            db.execSQL("update person set salary = ? where _id = ?", new String[]{"20000", "6"});

            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }
}
